package sql

import (
	"fmt"
)

var (
	VerifyUser = `
				SELECT 
				    count(*) 
				FROM 
				    users 
				WHERE 
				    id = ? AND token = ?`

	AddUserArticle = `
				INSERT INTO 
    					user_house(id,user_id,article_name,article_season,note,article_img,bar_code)
    			VALUES(
    			       ?,?,?,?,?,?,?
    			) `

	AddArticleClass = `
				INSERT INTO
					house_classify(id,goods_id, classify_name, note)
				VALUES (	
        			?,?,?,?
       			);`

	AddClassDetail = `
				INSERT INTO
					classify_detailed(id,classify_id, detailed_name, detailed_img,price)
				VALUES(
					   ?,?,?,?,?
      			)
`
	SearchArticle = `
				SELECT *
				FROM
					user_house u
				LEFT JOIN
					house_classify h
				ON
					u.id = h.goods_id
				LEFT JOIN
					classify_detailed c
				ON
					h.id = c.classify_id
`
	ArticleTotal = `
				SELECT COUNT(*) total FROM user_house WHERE user_id = ?;
`
	UpdataUserHouse = `
				UPDATE 
					user_house
				SET 
					article_name = ?,
					article_img = ?,
					article_season = ?,
					note=?,
					bar_code=?
				WHERE
					id = ?
`
	DropHouseClass = `
DELETE 
FROM
house_classify
WHERE
goods_id = ?
`
	DropUserHouse = `
DELETE FROM
user_house
WHERE
id = ?
`
	DropInv = `
DELETE FROM
inventory
WHERE
house_id = ?
`
	CountHouse = `
SELECT
COUNT(*)
FROM
sell_records
WHERE 
house_id = ?

`
	HouseDetail = `
SELECT
u.id ,
u.article_name ,
u.article_img,
u.article_season,
UNIX_TIMESTAMP(u.create_time),
u.note,
h.id h_id,
h.classify_name h_name,
h.note h_note,
c.id c_id,
c.detailed_name c_name,
c.detailed_img c_img,
c.price
FROM
user_house u
LEFT JOIN
house_classify h
ON
u.id = h.goods_id
LEFT JOIN
classify_detailed c
ON
c.classify_id = h.id
WHERE
u.id = ?
`
	HouseNum = `
SELECT
SUM(num)
FROM
inventory
WHERE 
house_id = ?
`
	AddInventory = `
INSERT INTO 
    sell_records(
                 id,
                 house_id,
                 sold_key,
                 open_price,
                 sell_price,
                 note,
                 clear,
                 class_key
                 ,num
                 )
VALUES(
	?,
	?,
	?,
	?,
	?,
	?,
	?,
	?,
	?
)
`
	ClassificationInventory = `
SELECT
num
FROM
inventory
WHERE
classify_id = ?
`
	LoginUser = `
SELECT 
    id
FROM 
    users 
WHERE 
    user_name = ? 
  AND 
    user_pwd = ?
`
	UpDateToken = `
UPDATE 
    users
SET 
    token = ?
WHERE 
	id = ?
`
)

type JointListArticle struct {
	body       string
	limit      string
	middle     string
	sortMethod string
	sortKey    string
}

func (j *JointListArticle) AddTerm(key string) {
	if len(j.middle) > 0 {
		key = "AND\n" + key
	}
	j.middle = fmt.Sprint(j.middle, "\n", key, "=", "?")
}

func (j *JointListArticle) Get() string {
	relMiddle := ""
	sort := ""
	if len(j.middle) > 0 {
		relMiddle = fmt.Sprint("\nWHERE", j.middle)
	}
	if len(j.sortKey) > 0 {
		sort = fmt.Sprint("\n", "ORDER BY ", j.sortKey, " ", j.sortMethod)
	}
	return fmt.Sprint(j.body, relMiddle, sort, "\n", j.limit)
}

func (j *JointListArticle) Sort(key, right string) {
	j.sortMethod = right
	j.sortKey = key
}

func (j *JointListArticle) Custom(key string) {
	if len(j.middle) > 0 {
		key = "AND\n" + key
	}
	j.middle = fmt.Sprint(j.middle, "\n", key)

}

func NewListArticle(id string) *JointListArticle {
	return &JointListArticle{
		body: fmt.Sprint(`
SELECT 
id,
article_name,
article_season,
create_time,
article_img,
note,
(SELECT IFNULL(SUM(num),0) FROM inventory WHERE house_id = o.id) sum,
IFNULL(price,0),
COUNT(*) OVER() total,
IFNULL(h.out_goods,0) out_goods,
IFNULL(h1.on_goods,0) on_goods
FROM
(SELECT 
DISTINCT u.id,
u.article_name,
u.article_season,
UNIX_TIMESTAMP(u.create_time) create_time,
IFNULL(u.article_img,"") article_img,
u.note,
IFNULL(SUM(i.num) OVER( PARTITION BY u.id),0) sum,
c.price
FROM
user_house u
LEFT JOIN
inventory i
ON
u.id = i.house_id`, " AND u.user_id = '", id, "'", `
LEFT JOIN
house_classify h
ON h.goods_id = u.id
LEFT JOIN
classify_detailed c
ON
c.classify_id = h.id 
) o
LEFT JOIN
(
SELECT
s.house_id,SUM(s.num) out_goods
FROM
sell_records s
WHERE 
s.clear = 0
GROUP BY
s.house_id
) h
ON
o.id = h.house_id
LEFT JOIN
(
SELECT 
s1.house_id,SUM(s1.num) on_goods
FROM 
sell_records s1 
WHERE 
s1.clear = 1 
GROUP BY 
s1.house_id
) h1
ON
h1.house_id =  o.id  
`),
		limit: `LIMIT ?,?`,
	}
}
